package sk.velkavrana.app.util;

import sk.velkavrana.app.model.ConnectionInfo;
import sk.velkavrana.app.model.temp.ColumnInfo;
import sk.velkavrana.app.model.temp.TableInfo;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * User: mtymes
 * Date: 31.10.2008
 * Time: 21:50:21
 */
public class DBUtil {


    public static Connection connect(String driver, String url, String login, String password) throws ClassNotFoundException, SQLException {
        Connection connection = null;

        // load JDBC driver
        Class.forName(driver);

        connection = DriverManager.getConnection(url, login, password);

        return connection;
    }


    public static Connection connectSilently(String driver, String url, String login, String password) {
        Connection connection = null;

        try {
            connection = connect(driver, url, login, password);
        } catch (ClassNotFoundException e) {
            // todo: maybe implement some other way
//            e.printStackTrace();
        } catch (SQLException e) {
            // todo: maybe implement some other way
//            e.printStackTrace();
        }

        return connection;
    }


    public static Connection connect(ConnectionInfo connectionInfo) throws ClassNotFoundException, SQLException {
        return connect(connectionInfo.getDriver(), connectionInfo.getUrl(), connectionInfo.getLogin(), connectionInfo.getPassword());
    }


    public static Connection connectSilently(ConnectionInfo connectionInfo) {
        return connectSilently(connectionInfo.getDriver(), connectionInfo.getUrl(), connectionInfo.getLogin(), connectionInfo.getPassword());
    }


    public static void getTypeInfo(Connection connection) {
        // todo: implement

        // gets the database metadata
        try {
            DatabaseMetaData dbmd = connection.getMetaData();

            ResultSet resultSet = dbmd.getTypeInfo();

            while (resultSet.next()) {
                // todo: implement
                System.out.println(resultSet.getInt("DATA_TYPE") + " : " + resultSet.getString("TYPE_NAME"));
            }

            resultSet.close();

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


    public static List<String> getSchemas(Connection connection) {
        List<String> schemas = new ArrayList<String>();

        try {
            // gets the database metadata
            DatabaseMetaData dbmd = connection.getMetaData();

            ResultSet resultSet = dbmd.getSchemas();

            // get the table names
            while (resultSet.next()) {
                schemas.add(resultSet.getString("TABLE_SCHEM"));
            }

            resultSet.close();

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return schemas;
    }


    public static List<TableInfo> getTables(Connection connection) {
        List<TableInfo> tables = new ArrayList<TableInfo>();

        try {
            // gets the database metadata
            DatabaseMetaData dbmd = connection.getMetaData();

            // specify the type of object; in this case we want tables
            String[] types = {"TABLE"};
            ResultSet resultSet = dbmd.getTables(null, /*connection.getMetaData().getUserName()*/ null, "%", types);

            // get the table names
            while (resultSet.next()) {
                // todo: process all parameters
                tables.add(new TableInfo(resultSet.getString("TABLE_NAME"), resultSet.getString("TABLE_CAT"), resultSet.getString("TABLE_SCHEM"), resultSet.getString("TABLE_TYPE"), resultSet.getString("REMARKS")));
            }

            resultSet.close();

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return tables;
    }


    public static List<ColumnInfo> getColumns(Connection connection, TableInfo table) {
        List<ColumnInfo> columns = new ArrayList<ColumnInfo>();

        try {
            // gets the database metadata
            DatabaseMetaData dbmd = connection.getMetaData();

            ResultSet resultSet = dbmd.getColumns(table.getCatalog(), table.getSchema(), table.getName(), "%");

            // get the column names
            while (resultSet.next()) {
                // todo: process all parameters
                columns.add(new ColumnInfo(resultSet.getString("COLUMN_NAME"), resultSet.getInt("DATA_TYPE"), resultSet.getString("TYPE_NAME"), resultSet.getInt("COLUMN_SIZE"), resultSet.getInt("DECIMAL_DIGITS"), resultSet.getBoolean("NULLABLE")));
            }

            resultSet.close();

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return columns;
    }


    public static List<Object[]> getRecords(Connection connection, TableInfo table, List<ColumnInfo> columns) {
        List<Object[]> records = new ArrayList<Object[]>();

        /////////////////////
        // generate sql query
        StringBuffer queryString = new StringBuffer("SELECT ");

        // add column names
        boolean first = true;
        for (ColumnInfo column : columns) {
            if (!first) {
                queryString.append(",");
            } else {
                first = false;
            }

            queryString.append("\"" + column.getName() + "\"");
        }

        queryString.append(" FROM " + table.getFullName());

        ////////////////////
        // execute sql query

        try {
            Statement statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery(queryString.toString());

            // get the records
            while (resultSet.next()) {
                Object record[] = new Object[columns.size()];

                for (int i = 0; i < columns.size(); i++) {
                    ColumnInfo column = columns.get(i);

                    // todo: retrieve column specific object type
                    switch (column.getDataType()) {
                        case Types.INTEGER:
                            record[i] = resultSet.getInt(column.getName());
                            break;
                        case Types.DECIMAL:
                            record[i] = resultSet.getBigDecimal(column.getName());
                            break;
                        case Types.VARCHAR:
                            record[i] = resultSet.getString(column.getName());
                            break;
                        default:
                            record[i] = resultSet.getString(column.getName());
                            break;
                    }
                }

                records.add(record);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }


        return records;
    }


    public static boolean closeSilently(Connection connection) {
        boolean success = true;

        try {
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
            success = false;
        }

        return success;
    }
}
